package io.renren.common.utils;

import io.renren.common.template.excel.ExcelSheet;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Excel导入工具
 *
 * @author Harway
 * @version v1.0
 */
public class ExcelImportUtil {
    private static Logger logger = LoggerFactory.getLogger(ExcelImportUtil.class);

    /**
     * 从Workbook导入Excel文件，并封装成对象
     *
     * @param workbook
     * @param sheetClass
     * @return List<Object>
     */
    public static <T> List<T> importExcel(Workbook workbook, Class<T> sheetClass) {
        return importSheet(workbook, sheetClass);
    }

    public static <T> List<T> importSheet(Workbook workbook, Class<T> sheetClass) {
        try {
            // sheet
            ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
            String sheetName = (excelSheet != null && excelSheet.name().trim().length() > 0) ? excelSheet.name().trim() : sheetClass.getSimpleName();

            // sheet field
            List<Field> fields = new ArrayList<Field>();
            if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
                for (Field field : sheetClass.getDeclaredFields()) {
                    if (Modifier.isStatic(field.getModifiers())) {
                        continue;
                    }
                    fields.add(field);
                }
            }

            if (fields.size() == 0) {
                throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
            }

            // sheet data
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                return null;
            }

            Iterator<Row> sheetIterator = sheet.rowIterator();
            int rowIndex = 0;
            List<T> dataList = new ArrayList<>();
            while (sheetIterator.hasNext()) {
                Row rowX = sheetIterator.next();
                if (rowIndex > 0) {
                    Object rowObj = sheetClass.newInstance();
                    for (int i = 0; i < fields.size(); i++) {

                        // cell
                        Cell cell = rowX.getCell(i);
                        if (cell == null) {
                            continue;
                        }

                        // call val str
                        cell.setCellType(CellType.STRING);
                        // cell.getCellTypeEnum()
                        String fieldValueStr = cell.getStringCellValue();

                        // java val
                        Field field = fields.get(i);
                        Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr);
                        if (fieldValue == null) {
                            continue;
                        }

                        // fill val
                        field.setAccessible(true);
                        field.set(rowObj, fieldValue);
                    }
                    dataList.add((T)rowObj);
                }
                rowIndex++;
            }
            return dataList;
        } catch (IllegalAccessException | InstantiationException e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 导入Excel文件，并封装成对象
     *
     * @param excelFile
     * @param sheetClass
     * @return List<Object>
     */
    public static <T> List<T> importExcel(File excelFile, Class<T> sheetClass) {
        try {
            Workbook workbook = WorkbookFactory.create(excelFile);
            return importExcel(workbook, sheetClass);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 从文件路径导入Excel文件，并封装成对象
     *
     * @param filePath
     * @param sheetClass
     * @return List<Object>
     */
    public static <T> List<T> importExcel(String filePath, Class<T> sheetClass) {
        File excelFile = new File(filePath);
        return importExcel(excelFile, sheetClass);
    }

    /**
     * 导入Excel数据流，并封装成对象
     *
     * @param inputStream
     * @param sheetClass
     * @return List<Object>
     */
    public static <T> List<T> importExcel(InputStream inputStream, Class<T> sheetClass) {
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            return importExcel(workbook, sheetClass);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据上传的文件，获取workBook对象
     *
     * @param file 上传的文件
     */
    public static Workbook getWorkBook(MultipartFile file) {
        //检验上传的文件是否正确
        String fileName = file.getOriginalFilename();
        if (StringUtils.isNotBlank(fileName)) {
            if (!fileName.endsWith("xlsx") && !fileName.endsWith("xls")) {
                throw new RuntimeException("文件格式错误");
            } else {
                //创建Workbook工作薄对象，表示整个excel
                Workbook workbook = null;
                try {
                    //获取excel文件的io流
                    InputStream is = file.getInputStream();
                    //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
                    if (StringUtils.isNotBlank(fileName) && fileName.endsWith("xls")) {
                        //2003
                        workbook = new HSSFWorkbook(is);
                    } else {
                        //2007
                        workbook = new XSSFWorkbook(is);
                    }
                } catch (IOException e) {
                    logger.info(e.getMessage());
                }
                return workbook;
            }
        }
        return null;
    }

    /**
     * 获取单元格内的值，以字符串的形式返回
     *
     * @param cell 单元格
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == CellType.NUMERIC) {
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
